using System;
using System.Collections.Generic;
using UnityEngine;

namespace UnityORM
{
	/// <summary>
	/// Schme management tool.
	/// Execute only diff sqls.
	/// </summary>
	public class DBEvolution
	{
		string dbFilePath;
		SqliteDatabase database;
		
		public SqliteDatabase Database {
			get {
				if (database == null) {
					database = new SqliteDatabase ();
					database.Open (dbFilePath);
					CreateMetaTable ();
				}
				return database;
			}
		}
		
		public bool RecreateTableIfHashDiffers{ get; set; }
		
		/// <summary>
		/// Initializes a new instance of the <see cref="com.geishatokyo.DB.DBEvolution"/> class.
		/// </summary>
		/// <param name='dbFilePath'>
		/// Db file path.
		/// </param>
		public DBEvolution (string dbFilePath)
		{
			this.dbFilePath = dbFilePath;
			RecreateTableIfHashDiffers = false;
		}
		
		/// <summary>
		/// Evolute the specified tableName with sqls.
		/// </summary>
		/// <param name='tableName'>
		/// Table name.
		/// </param>
		/// <param name='sqls'>
		/// Sqls.
		/// </param>
		public void Evolute (string tableName, List<string> sqls)
		{
			TableVersion version = GetTableVersion (tableName);
			var db = this.Database;
			if (RecreateTableIfHashDiffers && version.QueryHash != null) {
				string hash;
				if (sqls.Count > version.QueryIndex) {
					hash = GetHash (sqls.GetRange (0, version.QueryIndex + 1));
					
				} else {
					hash = "";
				}
				
				if (hash != version.QueryHash) {
					Debug.Log (string.Format ("Table hash error.OnDB:{0} Passed:{1}", version.QueryHash, hash));
					DropTable (tableName);
					version.QueryIndex = -1;
					version.QueryHash = null;
				}
			}
			
			//bool updated = false;
			int successIndex = -1;
			bool error = false;
			for (int i = version.QueryIndex + 1; i < sqls.Count; i++) {
				Debug.Log (string.Format ("Execute query of {0}:{1}", tableName, i));
				//updated = true;
				try {
					db.ExecuteNonQuery (sqls [i]);
					successIndex = i;
					
				} catch (SqliteException e) {
					error = true;
					Debug.Log ("Error SQL:" + sqls [i]);
					Debug.LogException (e);
					break;
				}
			}
			
			if (successIndex >= 0) {
				sqls = sqls.GetRange (0, successIndex + 1);
				if (version.QueryHash == null) {
					string hashCode = GetHash (sqls);
					CreateRecord (tableName, sqls.Count - 1, hashCode);
					
				} else {
					string hashCode = GetHash (sqls);
					UpdateRecord (tableName, sqls.Count - 1, hashCode);
				}
			}
			
			if (error) {
				Debug.Log ("There are some errors.Please fix your sql");
			}
			
		}
		
		public void Close ()
		{
			if (database != null) {
				database.Close ();
				database = null;
			}
		}
		
		string GetHash (List<string> sqls)
		{
			string join = string.Join ("_", sqls.ToArray ());
			return join.GetHashCode ().ToString ();
		}
		
		void CreateRecord (string tableName, int index, string queryHash)
		{
			string sql = string.Format (
				@"INSERT INTO MetaTable VALUES('{0}',{1},'{2}');", tableName, index, queryHash);
			Database.ExecuteNonQuery (sql);
		}
		
		void UpdateRecord (string tableName, int index, string queryHash)
		{
			string sql = string.Format (
				@"UPDATE MetaTable SET queryIndex={0},queryHash='{1}' WHERE tableName='{2}';",
				index, queryHash, tableName);
			Database.ExecuteNonQuery (sql);
		}
		
		void DropTable (string tableName)
		{
			Debug.Log ("DROP TABLE IF EXISTS " + tableName);
			string sql = string.Format (@"DROP TABLE IF EXISTS {0};", tableName);
			Database.ExecuteNonQuery (sql);
			
			string metaSql = string.Format (@"DELETE FROM MetaTable WHERE tableName='{0}';", tableName);
			Database.ExecuteNonQuery (metaSql);
		}
		
		TableVersion GetTableVersion (string tableName)
		{
			string sql = @"SELECT * FROM MetaTable WHERE tableName = '" + tableName + "';";
			DataTable dt = Database.ExecuteQuery (sql);
			
			TableVersion tv = new TableVersion ();
			if (dt.Rows.Count > 0) {
				tv.TableName = tableName;
				var row = dt.Rows [0];
				tv.QueryIndex = row.GetAsInt ("queryIndex");
				tv.QueryHash = row ["queryHash"].ToString ();
				
			} else {
				tv.TableName = tableName;
				tv.QueryIndex = -1;
				tv.QueryHash = null;
			}
			
			return tv;
		}
		
		bool CreateMetaTable ()
		{
			string createTable = @"CREATE TABLE IF NOT EXISTS MetaTable(
tableName VARCHAR(100) NOT NULL,
queryIndex Int NOT NULL,
queryHash VARCHAR(100) NOT NULL);";
			Database.ExecuteNonQuery (createTable);
			// TODO fix to check table is really created.
			return true;
		}
	}
	
	struct TableVersion
	{
		public string TableName;
		public int QueryIndex;
		public string QueryHash;
	}
}

